package com.gaga.crm.repository;

import com.gaga.crm.pojo.SysUser;
import com.gaga.crm.vo.UserInfo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface UserRepository extends JpaRepository<SysUser,Long> {

    /**
     * name查询
     * @param name
     * @return
     */
    List<SysUser> findByName(String name);

    /**
     * 通过Name模糊查找用户
     *
     * @return
     */
    List<SysUser> findByNameLike(String name);

    /**
     * 通过name统计数量
     * @param name
     * @return
     */
    int countByName(String name);

    /**
     * 通过Name属性和Password属性查询
     * <p>
     * select * from sys_user where usr_name=? and usr_password=?
     * @return
     */
    List<SysUser> findByNameAndPassword(String name, String password);

    /**
     * 使用自定义QL语句完成复杂查询
     *
     * @param roleId
     * @return
     */
    //@Query("select u from SysUser u where u.roleId=?1")                             //HQL: 与SQL语句的区别：将表名变为实体名称，将字段名变为属性名称
    //@Query(value = "select * from t_sys_user u where roleId=?1",nativeQuery = true) //想用原生SQL语句：nativeQuery = true
    @Query("select u from SysUser u where u.roleId=:roleId")                          //@Parem支持命名参数
    List<SysUser> findByRoleId(@Param("roleId")Long roleId);

    /**
     * 对事务的支持
     *
     * @param usrName
     * @param usrId
     * @return
     */
    @Transactional(timeout = 10)
    @Modifying
    @Query("update SysUser u set u.name=?1 where u.roleId=?2")
    int modifyNameById(String usrName, Long usrId);

    /**
     * 通过将方法名与定义的命名QL一致
     * @return
     */
    List<SysUser> findUsersByName(String name);

    /**
     * 分页查询
     *
     * @param roleId 自己的参数写在前面
     * @param pageable 内置的分页对象
     * @return Page（Page查询分页对象以及数据；Slice表示只查询一页数据）
     */
    @Query("select u from SysUser u where u.roleId=?1")
    Page<SysUser> findPageByRoleId(Long roleId, Pageable pageable);

    /**
     *分页查询（通过JpaSpecificationExecutor接口）所需方法
     * @param sysUserSpecification
     * @param pageable
     * @return
     */
    Page<SysUser> findAll(Specification<SysUser> sysUserSpecification, Pageable pageable);

    /**
     * 通过UserInfo自动生成代理类接受返回结果
     * 此处Query为HQL，必须面向实体和属性编写，获取到的属性必须指定别名
     * @param uId
     * @return
     */
    @Query("select u.id as id,u.name as name,u.password as password,u.roleId as roleId,u.flag as flag,r.role_name as roleName from SysUser u,SysRole r where u.roleId=r.role_id and u.id=?1")
    UserInfo getUserInfo(Long uId);
}
